package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import objects.DistanceLog;
import objects.GeneralLog;
import objects.Instructor;
import objects.NewStudent;
import objects.School;
import objects.SpeedLimit;
import objects.SpeedLog;
import objects.StopSign;
import objects.StopSignLog;
import objects.Student;

public class OBUdb {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public OBUdb() throws Exception {
	
		try {
			// This will load the MySQL driver, each DB has its own driver
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException X) {
				System.out.println("Driver could not be loadded!!!");
			}
			// Setup the connection with the DB
			String pw = "c$411r3d$p10";
			connect = DriverManager.getConnection("jdbc:mysql://raphael.cs.odu.edu/411redsp10new?" + "user=411redsp10&password="+pw);

		} catch (Exception e) {
			throw e;
		}
	}

	public void getSchool( School school1) throws SQLException {
        // Statements allow to issue SQL queries to the database
		String stmt1 = "SELECT * FROM 411redsp10new.School WHERE school_id = ?";
        preparedStatement = connect.prepareStatement(stmt1);
		preparedStatement.setInt(1,school1.getID());
        resultSet = preparedStatement.executeQuery();

		while (resultSet.next()) {
			school1.setAddress(resultSet.getString("address"));
			school1.setName(resultSet.getString("name"));
			school1.setOwner(resultSet.getString("owner"));
		}
	}

    public void getStudent( Student student1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt2 = "SELECT * FROM 411redsp10new.Student WHERE student_id = ?";
        preparedStatement = connect.prepareStatement(stmt2);
        preparedStatement.setInt(1,student1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            student1.setName(resultSet.getString("name"));
            student1.setInstrID(resultSet.getInt("instr_id"));
            student1.setSchoolID(resultSet.getInt("school_id"));
            student1.setHours(resultSet.getDouble("hours"));
            student1.setAddress(resultSet.getString("address"));
            student1.setFingerPrint(resultSet.getBlob("fingerprint"));
        }
    }

    public void getNewStudent( NewStudent newstudent1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt3 = "SELECT * FROM 411redsp10new.StudentNew WHERE student_id = ?";
        preparedStatement = connect.prepareStatement(stmt3);
        preparedStatement.setInt(1,newstudent1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            newstudent1.setName(resultSet.getString("name"));
            newstudent1.setInstrID(resultSet.getInt("instr_id"));
            newstudent1.setSchoolID(resultSet.getInt("school_id"));
            newstudent1.setHours(resultSet.getDouble("hours"));
            newstudent1.setAddress(resultSet.getString("address"));
            newstudent1.setFingerprint(resultSet.getBlob("fingerprint"));
        }
    }


    public void getInstructor( Instructor instructor1) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt4 = "SELECT * FROM 411redsp10new.Instructor WHERE instr_id = ?";
        preparedStatement = connect.prepareStatement(stmt4);
        preparedStatement.setInt(1,instructor1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
	        instructor1.setName(resultSet.getString("name"));
	        instructor1.setSchoolID(resultSet.getInt("school_id"));
        }
    }



    public void getSpeedLimit( SpeedLimit speedlimit1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt5 = "SELECT * FROM 411redsp10new.SpeedLimits WHERE location_id = ?";
        preparedStatement = connect.prepareStatement(stmt5);
        preparedStatement.setInt(1,speedlimit1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
	        speedlimit1.setP1Lat(resultSet.getDouble("point1Lat"));
	        speedlimit1.setP1Long(resultSet.getDouble("point1Long"));
	        speedlimit1.setP2Lat(resultSet.getDouble("point2Lat"));
	        speedlimit1.setP2Long(resultSet.getDouble("point2Long"));
	        speedlimit1.setLength(resultSet.getDouble("length"));
	        speedlimit1.setWidth(resultSet.getDouble("width"));
	        speedlimit1.setMaxSpeed(resultSet.getInt("maxspeed"));
        }
    }

    public void getStopSign( StopSign sts1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt7 = "SELECT * FROM 411redsp10new.StopSigns WHERE location_id = ?";
        preparedStatement = connect.prepareStatement(stmt7);
        preparedStatement.setInt(1,sts1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            sts1.setP1Lat(resultSet.getDouble("point1Lat"));
            sts1.setP1Long(resultSet.getDouble("point1Long"));
            sts1.setP2Lat(resultSet.getDouble("point2Lat"));
            sts1.setP2Long(resultSet.getDouble("point2Long"));
            sts1.setLength(resultSet.getDouble("length"));
            sts1.setWidth(resultSet.getDouble("width"));
            sts1.setTriggerCheck(resultSet.getBoolean("triggerCheck"));
        }
    }

    public void getStopSignLog( StopSignLog stsl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt9 = "SELECT * FROM 411redsp10new.StopLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt9);
        preparedStatement.setInt(1,stsl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            stsl1.setType(resultSet.getInt("type"));
            stsl1.setStudentID(resultSet.getInt("student_id"));
            stsl1.setLocationID(resultSet.getInt("location_id"));
            stsl1.setTimeSt(resultSet.getTimestamp("timestamp"));
        }
    }

    public void getGeneralLog( GeneralLog genl1 ) throws SQLException {
	    // Statements allow to issue SQL queries to the database
	    String stmt10 = "SELECT * FROM 411redsp10new.GenLogs WHERE log_id = ?";
	    preparedStatement = connect.prepareStatement(stmt10);
	    preparedStatement.setInt(1,genl1.getID());
	    resultSet = preparedStatement.executeQuery();
	
	    while (resultSet.next()) {
            genl1.setType(resultSet.getInt("type"));
            genl1.setStudentID(resultSet.getInt("student_id"));
            genl1.setLocationID(resultSet.getInt("location_id"));
            genl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            genl1.setDuration(resultSet.getInt("duration"));
	    }
    }

    public void getSpeedLog( SpeedLog spl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt11 = "SELECT * FROM 411redsp10new.SpeedLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt11);
        preparedStatement.setInt(1,spl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            spl1.setType(resultSet.getInt("type"));
            spl1.setStudentID(resultSet.getInt("student_id"));
            spl1.setLocationID(resultSet.getInt("location_id"));
            spl1.setCurrSpeed(resultSet.getInt("curr_speed"));
            spl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            spl1.setDuration(resultSet.getInt("duration"));
        }
    }

    public void getDistanceLog( DistanceLog disl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt12 = "SELECT * FROM 411redsp10new.DistanceLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt12);
        preparedStatement.setInt(1,disl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            disl1.setType(resultSet.getInt("type"));
            disl1.setStudentID(resultSet.getInt("student_id"));
            disl1.setLocationID(resultSet.getInt("location_id"));
            disl1.setCurrSpeed(resultSet.getInt("curr_speed"));
            disl1.setDistance(resultSet.getDouble("distance"));
            disl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            disl1.setDuration(resultSet.getInt("duration"));
        }
    }

    public void addStudent(Student st2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.Student(name,instr_id," +
        		"school_id,hours,address,fingerprint) values (?, ?, ?, ?, ?, ?)");
        preparedStatement.setString(1, st2.getName());
        preparedStatement.setInt(2, st2.getInstrID());
        preparedStatement.setInt(3, st2.getSchoolID());
        preparedStatement.setDouble(4, st2.getHours());
        preparedStatement.setString(5, st2.getAddress());
        preparedStatement.setBlob(6, st2.getFingerPrint());
        preparedStatement.executeUpdate();
    }

    public void addNewStudent(NewStudent st2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.StudentNew(name,instr_id," +
        		"school_id,hours,address,fingerprint) values (?, ?, ?, ?, ?, ?)");
        preparedStatement.setString(1, st2.getName());
        preparedStatement.setInt(2, st2.getInstrID());
        preparedStatement.setInt(3, st2.getSchoolID());
        preparedStatement.setDouble(4, st2.getHours());
        preparedStatement.setString(5, st2.getAddress());
        preparedStatement.setBlob(6, st2.getFingerPrint());
        preparedStatement.executeUpdate();
    }


    public void addSchool(School sc2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.School(address,name,owner) values (?, ?, ?)");
        preparedStatement.setString(1, sc2.getAddress());
        preparedStatement.setString(2, sc2.getName());
        preparedStatement.setString(3, sc2.getOwner());
        preparedStatement.executeUpdate();
    }


    public void addInstructor(Instructor instr2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.Instructor(name,school_id) values (?, ?)");
        preparedStatement.setString(1, instr2.getName());
        preparedStatement.setInt(2, instr2.getSchoolID());
        preparedStatement.executeUpdate();
    }

    public void addSpeedLimit(SpeedLimit spl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.SpeedLimits(point1Lat,point1Long," +
        		"point2Lat,point2Long,length,width,maxspeed) values (?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setDouble(1, spl2.getP1Lat());
        preparedStatement.setDouble(2, spl2.getP1Long());
        preparedStatement.setDouble(3, spl2.getP2Lat());
        preparedStatement.setDouble(4, spl2.getP2Long());
        preparedStatement.setDouble(5, spl2.getLength());
        preparedStatement.setDouble(6, spl2.getWidth());
        preparedStatement.setInt(7, spl2.getMaxSpeed());
        preparedStatement.executeUpdate();
    }

    public void addStopSign(StopSign sts2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.StopSigns(point1Lat,point1Long," +
        		"point2Lat,point2Long,length,width,triggerCheck) values (?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setDouble(1, sts2.getP1Lat());
        preparedStatement.setDouble(2, sts2.getP1Long());
        preparedStatement.setDouble(3, sts2.getP2Lat());
        preparedStatement.setDouble(4, sts2.getP2Long());
        preparedStatement.setDouble(5, sts2.getLength());
        preparedStatement.setDouble(6, sts2.getWidth());
        preparedStatement.setBoolean(7, sts2.getTriggerCheck());
        preparedStatement.executeUpdate();
    }


    public void addStopSignLog(StopSignLog stsl2) throws SQLException {
	    preparedStatement = connect.prepareStatement("insert into  411redsp10new.StopLogs(type,student_id,location_id)" +
	    		" values (?, ?, ?)");
	    preparedStatement.setInt(1, stsl2.getType());
	    preparedStatement.setInt(2, stsl2.getStudentID());
	    preparedStatement.setInt(3, stsl2.getLocationID());
	    preparedStatement.executeUpdate();
    }

    public void addGeneralLog(GeneralLog genl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.GenLogs(type,student_id,location_id," +
        		"duration) values (?, ?, ?, ?)");
        preparedStatement.setInt(1, genl2.getType());
        preparedStatement.setInt(2, genl2.getStudentID());
        preparedStatement.setInt(3, genl2.getLocationID());
        preparedStatement.setInt(4, genl2.getDuration());
        preparedStatement.executeUpdate();
    }

    public void addSpeedLog(SpeedLog splog2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.SpeedLogs(type,student_id," +
        		"location_id,curr_speed,duration) values (?, ?, ?, ?, ?)");
        preparedStatement.setInt(1, splog2.getType());
        preparedStatement.setInt(2, splog2.getStudentID());
        preparedStatement.setInt(3, splog2.getLocationID());
        preparedStatement.setInt(4, splog2.getCurrSpeed());
        preparedStatement.setInt(5, splog2.getDuration());
        preparedStatement.executeUpdate();
    }

    public void addDistanceLog(DistanceLog dsl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.DistanceLogs(type,student_id,location_id," +
        		"curr_speed,distance,duration) values (?, ?, ?, ?, ?, ?)");
        preparedStatement.setInt(1, dsl2.getType());
        preparedStatement.setInt(2, dsl2.getStudentID());
        preparedStatement.setInt(3, dsl2.getLocationID());
        preparedStatement.setInt(4, dsl2.getCurrSpeed());
        preparedStatement.setDouble(5, dsl2.getDistance());
        preparedStatement.setInt(6, dsl2.getDuration());
        preparedStatement.executeUpdate();
    }


    public Integer getNewMax(Double givenLat, Double givenLong) throws SQLException {
        statement = connect.createStatement();
        resultSet = statement.executeQuery("select * from 411redsp10new.SpeedLimits");

        while(resultSet.next()) {
			if ( (givenLat >= resultSet.getDouble("point1Lat")) && (givenLat <= resultSet.getDouble("point2Lat"))
                                    && (givenLong <= resultSet.getDouble("point2Long")) && (givenLong >= resultSet.getDouble("point1Long"))) {
				Integer newMax = resultSet.getInt("maxspeed");
				System.out.println("I found this in box " + resultSet.getInt("location_id"));
                return newMax;
            }
        }

        return 0;
    }

    public Integer detectStopSign(Double givenLat, Double givenLong) throws SQLException {
        statement = connect.createStatement();
        resultSet = statement.executeQuery("select * from 411redsp10new.StopSigns");

        while(resultSet.next()) {
	        if ( (givenLat >= resultSet.getDouble("point1Lat")) && (givenLat <= resultSet.getDouble("point2Lat"))
	                && (givenLong <= resultSet.getDouble("point2Long")) && (givenLong >= resultSet.getDouble("point1Long")) ){
	            Integer newID = resultSet.getInt("location_id");
	            return newID;
            }
        }

        return -1;
    }

    public void updateStudentHours(Student st2) throws SQLException {
        preparedStatement = connect.prepareStatement("update 411redsp10new.Student set hours=? where student_id=?");
        preparedStatement.setDouble(1, st2.getHours());
        preparedStatement.setInt(2, st2.getID());
        preparedStatement.executeUpdate();
    }

    public void addAlan() throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10new.Student(name,instr_id,school_id,hours,address,fingerprint) " +
        		"values ('Alan Hill',1,1,0,'1234 Some St. Somewhere, VA 12345',1100111)");
        preparedStatement.executeUpdate();
    }
    
	// You need to close the resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}
